unit ManagerDB;

interface

uses
  //SingletonTemplate,
  // FIB
  //FIBDatabase, pFIBDatabase, FIBQuery, pFIBCacheQueries, FIBDataSet,
  //pFIBDataSet,
  // FireDac
  uADCompClient,
  //
  Data.DB, SysUtils;

type
  { TManagerDB }

  TManagerDB = class(TObject)
  private
    fDB: TADConnection;
    fReadTransaction: TADTransaction;
    fUpdateTransaction: TADTransaction;
  protected

  public
    function ExecuteScalar(const ASQL: string; const AParams: array of Variant): Variant;
    function ExecSQL(const ASQL: String; const AParams: array of Variant): LongInt;
    function GetQueryForUse(const ASQL: String): TADQuery;
    function SelectData(const ASQL: String; const AParams: array of Variant) : TADQuery;
    function SetWriteOptions(dsOpen: Boolean = true; fSQL: string = '';
              Table: string = ''; KeyFields: string = 'CODE'; GeneratorName: string = '') : TADQuery;
    destructor Destroy; override;
    constructor Create;
  published

  end;

  TADQueryHelper = class helper for TADQuery
  private
    function GetWord(S: String; Nomer: integer; Ch: char): String;
  public
    procedure SetWriteOpt(dsOpen: Boolean = true; fSQL: string = '';
     Table: string = ''; KeyFields: string = 'CODE'; GeneratorName: string = '');
    function FN(const FieldName: string): TField;
  end;

implementation

{ TManagerDB }

uses cmDBUnit;

constructor TManagerDB.Create;
begin
  fDB := cmDb.con1;
  fReadTransaction := cmDb.ReadTransaction;
  fUpdateTransaction := cmDb.UpdateTransaction;
end;

destructor TManagerDB.Destroy;
begin

  inherited;
end;

function TManagerDB.ExecSQL(const ASQL: String;
  const AParams: array of Variant): LongInt;
begin
  try
    fUpdateTransaction.StartTransaction;
    Result:= fDB.ExecSQL(ASQL, AParams);
    fDB.Commit;
  except
    on e: Exception do begin
      fDB.Rollback;
      raise Exception.Create(e.Message);
    end;
  end;
end;

function TManagerDB.ExecuteScalar(const ASQL: string;
  const AParams: array of Variant): Variant;
begin
  try
    fUpdateTransaction.StartTransaction;
    Result:= fDB.ExecSQLScalar(ASQL, AParams);
    fDB.Commit;
  except
    on e: Exception do begin
      fDB.Rollback;
      raise Exception.Create(e.Message);
    end;
  end;
end;

function TManagerDB.GetQueryForUse(const ASQL: String): TADQuery;
var
  Q: TADQuery;
begin
  Q:= TADQuery.Create(nil);
  Q.Connection:= fDB;
  Q.SQL.Text:= ASQL;
  Result:= Q;
end;

function TManagerDB.SelectData(const ASQL: String; const AParams: array of Variant): TADQuery;
var
  Q: TADQuery;
begin
  Q:= TADQuery.Create(nil);
  Q.Connection:= fDB;
  Q.Open(ASQL,AParams);
  Result:= Q;
end;

function TManagerDB.SetWriteOptions(dsOpen: Boolean; fSQL, Table,
  KeyFields, GeneratorName: string): TADQuery;
var
  Q: TADQuery;
begin
  Q:= GetQueryForUse('fSQL');
  Q.SetWriteOpt(dsOpen, fSQL, Table, KeyFields, GeneratorName);
  Result:= Q;
end;

{ TADQueryHelper }

function TADQueryHelper.FN(const FieldName: string): TField;
begin

end;

function TADQueryHelper.GetWord(S: String; Nomer: integer; Ch: char): String;
var
  WPoz,i: integer;
begin
  WPoz:=1; Result:='';
  for i:=1 to Length(S) do
  begin
    if S[i]=Ch then begin
            {if not SetFlag then} Inc(WPoz);
                       //SetFlag:=true;
                    end
     else if WPoz=Nomer then begin
             Result:=Result+S[i];
            //SetFlag:=false;
             end {else SetFlag:=false};
     if WPoz>Nomer then Break;
  end;
end;

procedure TADQueryHelper.SetWriteOpt(dsOpen: Boolean; fSQL, Table, KeyFields, GeneratorName: string);
begin
  if fSQL <> '' then
     SQL.Text := fSQL;
  if (KeyFields='') then KeyFields := 'CODE';
  UpdateOptions.AutoIncFields:= KeyFields;
  if (Table='') then
    Table:= GetWord(self.Name,2,'_');
  if (GeneratorName='') then GeneratorName:=GetWord(Self.Name,2,'_')+'_GEN';
    UpdateOptions.KeyFields := KeyFields;
    UpdateOptions.GeneratorName := GeneratorName;
    UpdateOptions.UpdateTableName := Table;
    UpdateOptions.UpdateMode:= upWhereChanged;
    UpdateOptions.CheckRequired:= False;
    if dsOpen = true then
     Open;
end;

end.
